<CFSET theDate = DateFormat(Now(),"mm/dd/yyyy") & " " & TimeFormat(Now(),"HH:mm:ss")>
<CFSET dDate = DATEFORMAT(Now(),"mm/dd/yyyy")>
<CFMODULE TEMPLATE="#client.common_path#cfm_DBdateFormat.cfm" dateConvert="#dDate#">
<CFSET sDate1 = dateResult>

<CFSET sMessPostApproval="Leave Balance has been updated.">
<CFQUERY NAME="qryCINLEAVEAPPSM" DATASOURCE="#client.company_dsn#">
	SELECT *
	FROM CINLEAVEAPPSM
	WHERE ROUTEID = '#GetRecord.ROUTEID#'
</CFQUERY>

<CFMODULE TEMPLATE="#client.common_path#cfm_DBdateFormat.cfm" dateConvert="#qryCINLEAVEAPPSM.STARTINGDATE#">
<CFSET sDate2 = dateResult>
<CFMODULE TEMPLATE="#client.common_path#cfm_DBdateFormat.cfm" dateConvert="#qryCINLEAVEAPPSM.ENDINGDATE#">
<CFSET sDate3 = dateResult>

<CFIF isDefined("qryCINLEAVEAPPSM.STAFFPID")>
	<CFIF GetEntry.STAFFPID NEQ "">
		<CFSET formPID = qryCINLEAVEAPPSM.STAFFPID>
	<CFELSE>
		<CFSET formPID = qryCINLEAVEAPPSM.PERSONNELIDNO>
	</CFIF>
<CFELSE>
	<CFSET formPID = qryCINLEAVEAPPSM.PERSONNELIDNO>
</CFIF>


<CFIF qryCINLEAVEAPPSM.RecordCount>
	<CFINCLUDE TEMPLATE="copytoAPPSD.cfm.">
	<CFQUERY NAME="qryCLKLVCODE" DATASOURCE=#client.company_dsn#>
		SELECT WITHBALANCE
		FROM CLKLVCODE
		WHERE LEAVETYPE = '#qryCINLEAVEAPPSM.LEAVETYPE#'
	</CFQUERY>
	<!--- replace the current personnelidno with staffpid if existing --->
	<CFIF isDefined("qryCINLEAVEAPPSM.STAFFPID")>
		<CFIF GetEntry.STAFFPID NEQ "">
			<CFQUERY NAME="updateCINLEAVEAPPSMID" DATASOURCE="#client.company_dsn#">
				UPDATE CINLEAVEAPPSM
				SET PERSONNELIDNO = '#formPID#'
				WHERE ROUTEID = '#GetRecord.ROUTEID#'
			</CFQUERY>
			<CFQUERY NAME="updateCINLEAVEAPPSDID" DATASOURCE="#client.company_dsn#">
				UPDATE CINLEAVEAPPSD
				SET PERSONNELIDNO = '#formPID#'
				WHERE DOCNUMBER = '#qryCINLEAVEAPPSM.DOCNUMBER#'
			</CFQUERY>
		</CFIF>
	</CFIF>
	<CFIF NOT qryCLKLVCODE.RecordCount>
		<CFSET sMessPostApproval="WARNING!!! Leave Balance has not been updated. Leave Type doesn't exist in Leave Entitlement.<BR>Please contact your administrator.">
	<CFELSEIF Trim(Ucase(qryCLKLVCODE.WITHBALANCE)) IS "N">
		<CFSET sMessPostApproval="Leave Record has been updated.">
		<CFTRANSACTION>
		<CFTRY>
			<CFQUERY NAME="qryCINLVRECORD" DATASOURCE="#client.company_dsn#">
				INSERT INTO CINLVRECORD
					(PERSONNELIDNO, LEAVETYPE,
					 DATEENTERED, TRANCODE, LEAVESTART,
					 LEAVEEND, USERID, UNITVALUE, RECCREATEDBY, RECDATECREATED, DATELASTUPDATE, TIMELASTUPDATE)
				VALUES
					('#formPID#', '#qryCINLEAVEAPPSM.LEAVETYPE#',
					 '#DateFormat(Now(),"mm/dd/yyyy HH:mm:ss")#',
					 'AU',
					 #preserveSingleQuotes(sDate2)#,
					 #preserveSingleQuotes(sDate3)#,
					 '#client.userid#', #qryCINLEAVEAPPSM.LEAVEHOURS#,
					 '#client.userid#', '#DateFormat(Now(),"mm/dd/yyyy")#', '#DateFormat(Now(),"mm/dd/yyyy")#', '#TimeFormat(Now(),"HH:mm:ss")#')
			</CFQUERY>

			<CFTRANSACTION ACTION="COMMIT"/>
			<CFCATCH TYPE="Database">
				<CFTRANSACTION ACTION="ROLLBACK"/>
				<CFSET sMessPostApproval="Error in post-approval activity ...<br><br>#CFCATCH.message# #CFCATCH.DETAIL#<BR>#CFCATCH.SQLSTATE#<BR>#CFCATCH.SQL#">
			</CFCATCH>
		</CFTRY>
		</CFTRANSACTION>
	<CFELSE>
		<CFQUERY NAME="qryCBR201" DATASOURCE=#client.company_dsn# MAXROWS=1>
			SELECT LEAVETRACKUNIT
			FROM CBR201
		</CFQUERY>

		<CFIF qryCBR201.RecordCount>
			<!--- Check workingdayscode --->
			<CFQUERY NAME="qryCMFPA" DATASOURCE="#client.company_dsn#">
				SELECT WORKINGDAYSCODE
				FROM CMFPA
				WHERE PERSONNELIDNO = '#formPID#'
			</CFQUERY>

			<CFIF qryCMFPA.RecordCount>
				<CFQUERY NAME="qryCLKWORKINGDAYS" DATASOURCE="#client.company_dsn#">
					SELECT WORKINGHOURSPDAY,WORKINGHOURSFORLV
					FROM CLKWORKINGDAYS
					WHERE WORKINGDAYSCODE = '#qryCMFPA.WORKINGDAYSCODE#'
				</CFQUERY>

				<CFIF qryCLKWORKINGDAYS.RecordCount>
					<CFQUERY NAME="qryCINLVENTITLEMENT" DATASOURCE="#client.company_dsn#">
						SELECT *
						FROM CINLVENTITLEMENT
						WHERE PERSONNELIDNO = '#formPID#' AND
							  LEAVETYPE = '#qryCINLEAVEAPPSM.LEAVETYPE#'
					</CFQUERY>

					<CFIF qryCINLVENTITLEMENT.RecordCount>
						<CFIF (qryCINLEAVEAPPSM.LEAVEHOURS NEQ "" AND qryCINLEAVEAPPSM.LEAVEHOURS NEQ 0) OR
						(qryCINLEAVEAPPSM.NOOFDAYS NEQ "" AND qryCINLEAVEAPPSM.NOOFDAYS NEQ 0)>
							<CFSET nLVHours=qryCINLVENTITLEMENT.LEAVEBALANCE>

							<CFIF qryCINLEAVEAPPSM.WITHPAY EQ 'Y'>
								<CFIF Trim(LCase(qryCBR201.LEAVETRACKUNIT)) IS "by day">
									<CFSET nValueEquivalent = (abs(val(qryCINLEAVEAPPSM.NOOFDAYS)) * (val(qryCLKWORKINGDAYS.WORKINGHOURSFORLV) / val(qryCLKWORKINGDAYS.WORKINGHOURSPDAY))) + (abs(val(qryCINLEAVEAPPSM.LEAVEHOURS)) / qryCLKWORKINGDAYS.WORKINGHOURSFORLV)>
								<CFELSE>
									<CFSET nValueEquivalent = (abs(val(qryCINLEAVEAPPSM.NOOFDAYS)) * qryCLKWORKINGDAYS.WORKINGHOURSFORLV) + abs(val(qryCINLEAVEAPPSM.LEAVEHOURS))>
								</CFIF>
							<CFELSE>
								<CFSET nValueEquivalent = 0>
							</CFIF>

							<CFSET nLVBalance=nLVHours - nValueEquivalent>

							<CFIF qryCINLVENTITLEMENT.AVAILEDLEAVE NEQ "">
								<CFSET nTotAvailedLeave=qryCINLVENTITLEMENT.AVAILEDLEAVE + nValueEquivalent>
							<CFELSE>
								<CFSET nTotAvailedLeave=nValueEquivalent>
							</CFIF>
							<!--- Modified by Maan 08/09/2007 --->
							<!---
							<CFIF Trim(LCase(qryCBR201.LEAVETRACKUNIT)) IS "by day">
								<CFSET nLVHours=qryCINLVENTITLEMENT.LEAVEBALANCE * qryCLKWORKINGDAYS.WORKINGHOURSFORLV>
								<CFSET nDeductLVHrs=nLVHours - qryCINLEAVEAPPSM.LEAVEHOURS>
								<CFSET nLVBalance=nDeductLVHrs / qryCLKWORKINGDAYS.WORKINGHOURSFORLV>
							<CFELSE>
								<CFSET nLVHours=qryCINLVENTITLEMENT.LEAVEBALANCE>
								<CFSET nLVBalance=nLVHours - qryCINLEAVEAPPSM.LEAVEHOURS>
							</CFIF>
							--->

							<!--- Deduct leave --->
							<!--- Start --->
							<CFTRANSACTION>
							<CFTRY>
								<CFQUERY NAME="qryDeductLeave" DATASOURCE="#client.company_dsn#">
									UPDATE CINLVENTITLEMENT
									SET LEAVEBALANCE = #nLVBalance#,
										LEAVEBALANCEASOF = #preserveSingleQuotes(sDate1)#,
										AVAILEDLEAVE = '#nTotAvailedLeave#',
										AVAILEDLEAVEASOF = #preserveSingleQuotes(sDate1)#
									WHERE PERSONNELIDNO = '#formPID#' AND
										  LEAVETYPE = '#qryCINLEAVEAPPSM.LEAVETYPE#'
								</CFQUERY>
								<CFQUERY NAME="qryCINLVRECORD" DATASOURCE="#client.company_dsn#">
									INSERT INTO CINLVRECORD
										(PERSONNELIDNO, LEAVETYPE,
										 DATEENTERED, TRANCODE, LEAVESTART,
										 LEAVEEND, UNITVALUE, VALUEEQUIVALENT,
										 ACCRUEDBALANCE, AVAILBALANCE, USERID, RECCREATEDBY, RECDATECREATED, DATELASTUPDATE, TIMELASTUPDATE)
									VALUES
										('#formPID#', '#qryCINLEAVEAPPSM.LEAVETYPE#',
										 '#DateFormat(Now(),"mm/dd/yyyy HH:mm:ss")#',
										 'AU',
										 #preserveSingleQuotes(sDate2)#,
										 #preserveSingleQuotes(sDate3)#,
										 '#nValueEquivalent#', '#nValueEquivalent#',
										 <CFIF Trim(qryCINLVENTITLEMENT.ACCRUEDBALANCE) IS "">0<CFELSE>#qryCINLVENTITLEMENT.ACCRUEDBALANCE#</CFIF>, #nLVBalance#, '#client.userid#'
										 , '#client.userid#', '#DateFormat(Now(),"mm/dd/yyyy")#', '#DateFormat(Now(),"mm/dd/yyyy")#', '#TimeFormat(Now(),"HH:mm:ss")#')
								</CFQUERY>
								<CFQUERY NAME="getApprover" DATASOURCE="#client.company_dsn#">
										SELECT COUNT(*) AS ORDERID
										FROM  CRGROUTEDETAILS
										WHERE ROUTEID = '#GetRecord.ROUTEID#'
								</CFQUERY>
								<CFQUERY NAME="getApproverMain" DATASOURCE="#client.company_dsn#">
										SELECT *
										FROM  CRGROUTEDETAILS
										WHERE ROUTEID = '#GetRecord.ROUTEID#' AND ORDERID='#getApprover.ORDERID#'
								</CFQUERY>
								<CFQUERY NAME="updateCINLEAVEAPPSM" DATASOURCE="#client.company_dsn#">
										UPDATE CINLEAVEAPPSM
										SET APPROVERMAIN = '#getApproverMain.ROUTER#'
										WHERE PERSONNELIDNO = '#formPID#' AND ROUTEID = '#GetRecord.ROUTEID#'
								</CFQUERY>
								<CFQUERY NAME="updateCINLEAVEAPPSD" DATASOURCE="#client.company_dsn#">
									UPDATE CINLEAVEAPPSD
									SET APPROVERMAIN = '#getApproverMain.ROUTER#'
									, APPROVED='Y'
									WHERE PERSONNELIDNO = '#formPID#' AND DOCNUMBER = '#qryCINLEAVEAPPSM.DOCNUMBER#'
								</CFQUERY>

								<CFTRANSACTION ACTION="COMMIT"/>
								<CFCATCH TYPE="Database">
									<CFTRANSACTION ACTION="ROLLBACK"/>
									<CFSET sMessPostApproval="Error in post-approval activity ...<br><br>#CFCATCH.message# #CFCATCH.DETAIL#<BR>#CFCATCH.SQLSTATE#<BR>#CFCATCH.SQL#">
								</CFCATCH>
							</CFTRY>
							</CFTRANSACTION>
							<!--- End --->


						<CFELSE>
							<CFSET sMessPostApproval="WARNING!!! Leave Balance has not been updated. Leave hours/days is 0.<BR>Please contact your administrator.">
						</CFIF>	<!--- LeaveType not found in CINLVENTITLEMENT --->
					<CFELSE>
						<CFSET sMessPostApproval="WARNING!!! Leave Balance has not been updated. Leave Type is missing in Leave Entitlement table.<BR>Please contact your administrator.">
					</CFIF>	<!--- LeaveType not found in CINLVENTITLEMENT --->
				<CFELSE>
					<CFSET sMessPostApproval="WARNING!!! Leave Balance has not been updated. WorkingDaysCode of employee is invalid.<BR>Please contact your administrator.">
				</CFIF> <!--- WorkingDaysCode of employee is invalid --->
			<CFELSE>
				<CFSET sMessPostApproval="WARNING!!! Leave Balance has not been updated. Employee is missing in master-file.<BR>Please contact your administrator.">
			</CFIF>	<!--- Employee not found in CMFPA --->
		<CFELSE>
			<CFSET sMessPostApproval="WARNING!!! Leave Balance has not been updated. Leave Track Unit is not properly configured.<BR>Please contact your administrator.">
		</CFIF>
	</CFIF>
<CFELSE>
	<CFSET sMessPostApproval="WARNING!!! Leave Balance has not been updated. Leave Application is missing.<BR>Please contact your administrator.">
</CFIF> <!--- RouteID not found in CINLVAPPSI --->